package com.yyyzzz.DAO.impl;

import com.yyyzzz.DAO.teacherDao;
import com.yyyzzz.bean.Course;
import com.yyyzzz.bean.Teacher;
import org.junit.Test;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class teacherDaoimpl implements teacherDao {
    @Override
    public int insterteacher (Teacher teacher) throws ClassNotFoundException, SQLException {
        //连接数据库
        String url = "jdbc:mysql://localhost:3306/yyyzzz?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true";
        String username = "root";
        String password = "123456";
        Class.forName ("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection (url , username , password);
        System.out.println (connection);
        Statement statement = connection.createStatement ();
        String sql = "insert into teacher(teacherNo, teachername, teacherpassword, teacherjob, sex) VALUES (?,?,?,?,?);";
        PreparedStatement preparedStatement = connection.prepareStatement (sql);
        preparedStatement.setInt (1 , teacher.getTeacherNo ());
        preparedStatement.setString (2 , teacher.getTeachername ());
        preparedStatement.setString (3 , teacher.getTeacherpassword ());
        preparedStatement.setString (4 , teacher.getTeacherjob ());
        preparedStatement.setString (5 , teacher.getSex ());
        int i = preparedStatement.executeUpdate ();
        if ( i > 0 ) {
            System.out.println ("插入成功！！！");
            preparedStatement.close ();
            statement.close ();
            connection.close ();
        }
        return 0;
    }

    @Override
    public int deleteTeacher (int teacherNo) throws ClassNotFoundException, SQLException {
        String url = "jdbc:mysql://localhost:3306/yyyzzz?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true";
        String username = "root";
        String password = "123456";
        Class.forName ("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection (url , username , password);
        System.out.println (connection);
        String sql = "delete from teacher where teacherNo=?;";
        PreparedStatement preparedStatement = connection.prepareStatement (sql);
        preparedStatement.setInt (1 , teacherNo);
        int i = preparedStatement.executeUpdate ();
        if ( i > 0 ) {
            System.out.println ("删除成功！！！");
            preparedStatement.close ();
            connection.close ();
        }
        return 0;
    }

    public Teacher getTeacherById (int teacherNo) throws ClassNotFoundException, SQLException {
        String url = "jdbc:mysql://localhost:3306/yyyzzz?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true";
        String username = "root";
        String password = "123456";
        Class.forName ("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection (url , username , password);
        System.out.println (connection);
        PreparedStatement preparedStatement = connection.prepareStatement ("select * from teacher where teacherNo=?;");
        preparedStatement.setInt (1 , teacherNo);
        preparedStatement.executeQuery ();
        ResultSet rs = preparedStatement.getResultSet ();
        while (rs.next ()) {
            String sex = rs.getString ("sex");
            String teachername = rs.getString ("teachername");
            String teacherpassword = rs.getString ("teacherpassword");
            String teacherjob = rs.getString ("teacherjob");
            int no = rs.getInt ("teacherNo");
            System.out.println ("教师号：" + rs.getObject ("teacherNo"));
            System.out.println ("教师姓名：" + rs.getObject ("teachername"));
            System.out.println ("密码：" + rs.getObject ("teacherpassword"));
            System.out.println ("教师职称：" + rs.getObject ("teacherjob"));
            System.out.println ("性别：" + rs.getObject ("sex"));
            System.out.println ();
            Teacher teacher = new Teacher (no , teachername , teacherpassword , teacherjob , sex);
            System.out.println (teacher);
            return teacher;
        }
        rs.close ();
        preparedStatement.close ();
        connection.close ();
        return null;
    }

    public Teacher check (int id) throws SQLException, ClassNotFoundException {
        teacherDaoimpl teacherDaoimpl = new teacherDaoimpl ();
        Teacher teacher = teacherDaoimpl.getTeacherById (id);
        if ( teacher == null ) {
            System.out.println ("继续");
        } else {
            System.out.println ("该教师号已存在，请重新输入教师号！");
        }
        return teacher;
    }

    @Override
    public int modifyTeacher (Teacher teacher) throws Exception {
        String url = "jdbc:mysql://localhost:3306/yyyzzz?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true";
        String username = "root";
        String password = "123456";
        Class.forName ("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection (url , username , password);
        PreparedStatement preparedStatement = connection.prepareStatement ("update teacher set teachername=?,teacherpassword=?,teacherjob=?,sex=? where teacherNo=?;");
        preparedStatement.setString (1 , teacher.getTeachername ());
        preparedStatement.setString (2 , teacher.getTeacherpassword ());
        preparedStatement.setString (3 , teacher.getTeacherjob ());
        preparedStatement.setString (4 , teacher.getSex ());
        preparedStatement.setString (5 , String.valueOf (teacher.getTeacherNo ()));
        int i = preparedStatement.executeUpdate ();
        System.out.println (i);
        preparedStatement.close ();
        connection.close ();
        return i;
    }

    @Test
    @Override
    public List getList (int teacherNo) throws Exception {
        teacherDaoimpl teacherDaoimpl = new teacherDaoimpl ();
        Teacher teacherById = teacherDaoimpl.getTeacherById (teacherNo);
        System.out.println (teacherById);
        ArrayList<Teacher> list = new ArrayList<> ();
        list.add (teacherById);
        System.out.println (list);
        return list;
    }

    @Override
    public List getList (String courseteacher) throws Exception {
        String url = "jdbc:mysql://localhost:3306/yyyzzz?serverTimezone=UTC&useUnicode=true&characterEncoding=utf-8&useSSL=false&allowPublicKeyRetrieval=true";
        String username = "root";
        String password = "123456";
        Class.forName ("com.mysql.cj.jdbc.Driver");
        Connection connection = DriverManager.getConnection (url , username , password);
        PreparedStatement preparedStatement = connection.prepareStatement ("select * from course where courseteacher=?;");
        preparedStatement.setString (1 , courseteacher);
        ResultSet rs = preparedStatement.executeQuery ();
        List<Course> courses = new ArrayList<> ();
        while (rs.next ()) {
            Course course = new Course ();
            course.setCourseId (rs.getString ("courseid"));
            course.setCourseName (rs.getString ("coursename"));
            course.setCourseTeacher (rs.getString ("courseteacher"));
            course.setClassroom (rs.getString ("classroom"));
            course.setCredit (rs.getDouble ("credit"));
            courses.add (course);
        }
        return courses;
    }
}

